package DBLayer;

import Models.Project.*;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DBLayer1 {
	
	
	public void postProjects(ArrayList param,ArrayList<String> paramType)
	{
		 try{     
            String query = "INSERT INTO Projects(ProjectName,ProjectDescription,ProjectCategory) VALUES(? , ? , ?)";
            DBConnection.execute(query, param, paramType);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
	}
	public ArrayList<Project> viewprojects() throws SQLException {
        String query = "SELECT ProjectName,ProjectId FROM Projects WHERE ProjectStatus = 'new'";
        
		Connection conn = DBConnection.getConnection();
		ResultSet result = DBConnection.executeQuery(query, conn);
		
		ArrayList<Project> Ap = new ArrayList<Project>();
		
       while(result.next())
       {
    	   Project p = new Project();
    	   p.setProjectName(result.getString("ProjectName"));
    	   p.setProjectId(result.getInt("ProjectId"));
    	   Ap.add(p);
       }
       conn.close();
       result.close();
       return Ap;
	}
	public Project projectDetail(int pid) throws SQLException {
        String query = "SELECT * FROM Projects WHERE ProjectId = '"+ pid +"'";
		Connection conn = DBConnection.getConnection();
		ResultSet result = DBConnection.executeQuery(query, conn);
		Project p = new Project();
       while(result.next())
       {
    	   p.setProjectId(result.getInt("ProjectId"));
    	   p.setProjectName(result.getString("ProjectName"));
    	   p.setProjectDescription(result.getString("ProjectDescription"));
    	   p.setProjectCategory(result.getString("ProjectCategory"));
       }
       conn.close();
       result.close();
       return p;
	}
	public int addComment(ArrayList param,ArrayList<String> paramType){
		int recordsAffected = 0;
		try{     
            String query = "INSERT INTO Comments(Comments,ProjectId) VALUES(? , ?)";
            recordsAffected = DBConnection.execute(query, param, paramType);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
		return recordsAffected;
	}
	public ArrayList<String> getAllComments(int pid) throws SQLException {
		String query = "SELECT * FROM Comments WHERE ProjectId = '"+ pid +"'";
		Connection conn = DBConnection.getConnection();
		ResultSet result = DBConnection.executeQuery(query, conn);
		ArrayList<String> comments = new ArrayList<String>();
       while(result.next())
       {
    	   comments.add(result.getString("Comments"));
       }
       conn.close();
       result.close();
       return comments;
	}
	
}
